﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.OleDb;

namespace LoadExcel
{
    class LoadExcel
    {
        DataSet mData = new DataSet();

        public void ExcelToDS(string Path)
        {            
            var files = System.IO.Directory.EnumerateFiles(Path,"*.xlsx");


            foreach (var filePath in files)
            {
                ExcelToDSByFile(filePath);
            }            
        }

        public void ExcelToDSByFile(string filePath)
        {
            if (filePath.Contains('~'))
                return;

            var ds = mData;

            string strConn = "Provider=Microsoft.ACE.OLEDB.12.0;" + "Data Source=" + filePath + ";" + "Extended Properties='Excel 12.0;HDR=Yes; IMEX=1';";
            OleDbConnection conn = new OleDbConnection(strConn);
            conn.Open();

            DataTable schemaTable = conn.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables, null);
            foreach (var tab in schemaTable.Rows)
            {
                var s = tab as DataRow;
                string tableName = s[2].ToString().Trim().Trim('$');
                if (!tableName.Contains('@') && !tableName.Contains("=映射"))
                    continue;

                string strExcel = string.Format("select * from [{0}]", tableName);
                OleDbDataAdapter myCommand = new OleDbDataAdapter(strExcel, strConn);
                var realName = tableName.Substring(1, tableName.Length - 3);
                myCommand.Fill(ds, realName);
            }

            conn.Close();
        }
    }
}
